import smtplib
import pandas as pd
from email.message import EmailMessage
import os
import time
import re
from dotenv import load_dotenv

# ========== 加载环境变量 ==========
load_dotenv(".env")  # 确保 .env 文件与此脚本同目录

SENDER_EMAIL = os.getenv('SENDER_EMAIL')
EMAIL_PASSWORD = os.getenv('EMAIL_PASSWORD')

# ========== 邮箱格式校验 ==========
def is_valid_email(email):
    pattern = re.compile(r"[^@]+@[^@]+\.[^@]+")
    return pattern.match(email)

# ========== 连接 SMTP ==========
def connect_smtp():
    print("🔧 正在建立SMTP连接...")
    smtp = smtplib.SMTP_SSL('smtp.qq.com', 465, timeout=30)
    smtp.login(SENDER_EMAIL, EMAIL_PASSWORD)
    print("✅ SMTP连接成功")
    return smtp

# ========== 发送单封邮件 ==========
def send_email(smtp, name, recipient, invoice_path):
    if not os.path.exists(invoice_path):
        print(f"❌ 发票未找到：{invoice_path}")
        return False

    if not is_valid_email(recipient):
        print(f"❌ 无效邮箱地址：{recipient}")
        return False

    msg = EmailMessage()
    msg['Subject'] = '您的电子发票'
    msg['From'] = SENDER_EMAIL
    msg['To'] = recipient
    msg.set_content(f'{name}，您好！附件是您的电子发票，请查收。')

    with open(invoice_path, 'rb') as f:
        msg.add_attachment(
            f.read(),
            maintype='application',
            subtype='pdf',
            filename=os.path.basename(invoice_path)
        )

    # 带重试机制
    for attempt in range(3):
        try:
            smtp.send_message(msg)
            print(f"✅ 发送成功：{name} <{recipient}>")
            return True
        except Exception as e:
            wait_time = 10 * (attempt + 1)
            print(f"⚠️ 第 {attempt+1} 次发送失败，{wait_time} 秒后重试... 错误：{e}")
            time.sleep(wait_time)
            try:
                smtp = connect_smtp()
            except Exception as retry_error:
                print(f"🚫 重连失败：{retry_error}")
                break

    print(f"❌ 重试失败：{name} <{recipient}>")
    return False

# ========== 主逻辑 ==========
def main():
    try:
        df = pd.read_excel('contacts.xlsx')
        total_sent = 0
        total_failed = 0
        send_count = 0
        max_per_session = 10

        smtp = connect_smtp()

        for index, row in df.iterrows():
            if send_count >= max_per_session:
                smtp.quit()
                print("⏳ 达到每轮最大发送数，等待30秒后重连...")
                time.sleep(30)
                smtp = connect_smtp()
                send_count = 0

            name = str(row.get('姓名')).strip()
            recipient = str(row.get('邮箱')).strip()
            invoice_path = str(row.get('发票路径')).strip()

            success = send_email(smtp, name, recipient, invoice_path)
            if success:
                total_sent += 1
                send_count += 1
            else:
                total_failed += 1

            if (index + 1) % 5 == 0:
                print("⌛ 每5封邮件，暂停10秒防止触发限制...")
                time.sleep(10)

        smtp.quit()
        print(f"\n📊 全部完成：成功 {total_sent} 封，失败 {total_failed} 封")

    except Exception as e:
        print(f"🔥 全局异常：{e}")

# ========== 启动 ==========
if __name__ == '__main__':
    main()
